Storing and Processing Temporal Data in Main Memory Column Stores

Managing and accessing temporal data is of increasing importance for many applications in industry. Yet, even though there was a significant amount of research in academia during the 1990’s, temporal features were only recently included into the SQL:2011 standard. Therefore, only a few temporal operators and with rather poor performance are currently implemented by commercial database systems. As several important use cases are currently not covered adequately by database systems, many developers model the time dimension on the application layer, rather than pushing down the operators to the database. The implementation of temporal features on the application layer leads to considerable performance overhead. The goal of this dissertation is to develop native support of temporal features for SAP HANA, a commercial in-memory column store database system. As no standard benchmark for temporal databases is available, we propose a new benchmark (TPC-BiH) which allows us to evaluate the performance of both commercial database systems and our own implementations. We investigate different alternatives to store temporal data physically in main memory and analyze the trade-offs arising from different memory layouts that cluster the data either by time or by space dimension. Taking into account the underlying physical representation, different temporal operators such as temporal aggregation, timeslice and temporal join have to be executed efficiently. We present a novel data structure called Timeline Index and algorithms based on this index, which have very competitive performance for all temporal operators. These algorithms beat existing best-of-breed approaches for each operator – in some cases by several orders of magnitude. While analysing the requirements with clients, it appeared that many applications include more than one time dimension. User-defined time domains, such as the validity of a contract or the availability of a product, are modeled as an applicationtime domain, whereas the period when a fact was visible in the database is represented by the system-time. For this reason we provide a bitemporal extension of the Timeline Index for bitemporal data. The Timeline Index is currently being integrated into SAP HANA.